﻿using WMSSyncData.Model;
using log4net;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace WMSSyncData.Utils
{
    public class OracleDBHelper
    {
        private readonly static ILog _logger = LogManager.GetLogger(typeof(OracleDBHelper));
        public static OracleCommand cmd = null;
        public static OracleConnection conn = null;
        public static string connstr = ConfigurationManager.ConnectionStrings["oracleconn"].ConnectionString;

        #region 建立数据库连接对象
        /// <summary>
        /// 建立数据库连接
        /// </summary>
        /// <returns>返回一个数据库的连接OracleConnection对象</returns>
        public static OracleConnection Create()
        {
            OracleConnection cn;
            try
            {
                cn = new OracleConnection(connstr);
                if (cn.State != ConnectionState.Open)
                {
                    cn.Open();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message.ToString());
            }
            return cn;
        }
        #endregion

        #region 建立数据库连接对象
        /// <summary>
        /// 建立数据库连接
        /// </summary>
        /// <returns>返回一个数据库的连接OracleConnection对象</returns>
        public static OracleConnection init()
        {
            try
            {
                conn = new OracleConnection(connstr);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message.ToString());
            }
            return conn;
        }
        #endregion

        #region 设置OracleCommand对象
        /// <summary>
        /// 设置OracleCommand对象 
        /// </summary>
        /// <param name="cmd">OracleCommand对象 </param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdParms">参数集合</param>
        private static void SetCommand(OracleCommand cmd, string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
        {
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                cmd.Parameters.AddRange(cmdParms);
            }
        }
        #endregion

        #region 执行相应的sql语句，返回相应的DataSet对象
        /// <summary>
        /// 执行相应的sql语句，返回相应的DataSet对象
        /// </summary>
        /// <param name="sqlstr">sql语句</param>
        /// <returns>返回相应的DataSet对象</returns>
        public static DataSet GetDataSet(string sqlstr)
        {
            DataSet set = new DataSet();
            try
            {
                using (var conns = new OracleConnection(connstr))
                {
                    conns.Open();
                    OracleDataAdapter adp = new OracleDataAdapter(sqlstr, conns);
                    adp.Fill(set);
                    conns.Close();
                }
            }
            catch (Exception e)
            {
                _logger.Error(e.Message.ToString());
            }
            return set;
        }
        #endregion

        #region 执行相应的sql语句，返回相应的DataSet对象
        /// <summary>
        /// 执行相应的sql语句，返回相应的DataSet对象
        /// </summary>
        /// <param name="sqlstr">sql语句</param>
        /// <param name="tableName">表名</param>
        /// <returns>返回相应的DataSet对象</returns>
        public static DataSet GetDataSet(string sqlstr, string tableName)
        {
            DataSet set = new DataSet();
            try
            {
                init();
                OracleDataAdapter adp = new OracleDataAdapter(sqlstr, conn);
                adp.Fill(set, tableName);
                conn.Close();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message.ToString());
            }
            return set;
        }
        #endregion

        public static int ExecuteNonQuery(OracleConnection conn, OracleTransaction tran, string cmdText)
        {
            int count = 0;
            try
            {
                cmd = new OracleCommand(cmdText, conn);
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();
                count = 1;
            }
            catch (Exception ex)
            {
                _logger.Error(ex.Message.ToString() + " Sql:" + cmdText);
            }
            return count;
        }

        #region 执行不带参数sql语句，返回所影响的行数
        /// <summary>
        /// 执行不带参数sql语句，返回所影响的行数
        /// </summary>
        /// <param name="cmdstr">增，删，改sql语句</param>
        /// <returns>返回所影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText)
        {
            int count = 0;
            try
            {
                using (var conns = new OracleConnection(connstr))
                {
                    conns.Open();
                    cmd = new OracleCommand(cmdText, conns);
                    cmd.ExecuteNonQuery();
                    conns.Close();
                    count = 1;
                }
            }
            catch (Exception ex)
            {
                //主键重复
                if (ex.Message.ToString().StartsWith("ORA-00001"))
                {
                    count = -1;
                }
                _logger.Error(ex.Message.ToString() + " Sql:" + cmdText);
            }
            return count;
        }
        #endregion

        #region 执行带参数sql语句或存储过程，返回所影响的行数
        /// <summary>
        /// 执行带参数sql语句或存储过程，返回所影响的行数
        /// </summary>
        /// <param name="cmdText">带参数的sql语句和存储过程名</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdParms">参数集合</param>
        /// <returns>返回所影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
        {
            int count;
            try
            {
                using (var conns = new OracleConnection(connstr))
                {
                    conns.Open();
                    cmd = new OracleCommand();
                    SetCommand(cmd, cmdText, cmdType, cmdParms);
                    count = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    conns.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return count;
        }
        #endregion

        #region 执行不带参数sql语句，返回一个从数据源读取数据的OracleDataReader对象
        /// <summary>
        /// 执行不带参数sql语句，返回一个从数据源读取数据的OracleDataReader对象
        /// </summary>
        /// <param name="cmdstr">相应的sql语句</param>
        /// <returns>返回一个从数据源读取数据的OracleDataReader对象</returns>
        public static OracleDataReader ExecuteReader(string cmdText)
        {
            OracleDataReader reader;
            try
            {
                init();
                cmd = new OracleCommand(cmdText, conn);
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return reader;
        }
        #endregion

        #region 执行带参数的sql语句或存储过程，返回一个从数据源读取数据的OracleDataReader对象
        /// <summary>
        /// 执行带参数的sql语句或存储过程，返回一个从数据源读取数据的OracleDataReader对象
        /// </summary>
        /// <param name="cmdText">sql语句或存储过程名</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdParms">参数集合</param>
        /// <returns>返回一个从数据源读取数据的OracleDataReader对象</returns>
        public static OracleDataReader ExecuteReader(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
        {
            OracleDataReader reader;
            try
            {
                init();
                cmd = new OracleCommand();
                SetCommand(cmd, cmdText, cmdType, cmdParms);
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return reader;
        }
        #endregion

        #region 执行不带参数sql语句,返回结果集首行首列的值object
        /// <summary>
        /// 执行不带参数sql语句,返回结果集首行首列的值object
        /// </summary>
        /// <param name="cmdstr">相应的sql语句</param>
        /// <returns>返回结果集首行首列的值object</returns>
        public static object ExecuteScalar(string cmdText)
        {
            object obj;
            try
            {
                init();
                cmd = new OracleCommand(cmdText, conn);
                obj = cmd.ExecuteScalar();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return obj;
        }
        #endregion

        #region 执行带参数sql语句或存储过程,返回结果集首行首列的值object
        /// <summary>
        /// 执行带参数sql语句或存储过程,返回结果集首行首列的值object
        /// </summary>
        /// <param name="cmdText">sql语句或存储过程名</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdParms">返回结果集首行首列的值object</param>
        /// <returns></returns>
        public static object ExecuteScalar(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
        {
            object obj;
            try
            {
                init();
                cmd = new OracleCommand();
                SetCommand(cmd, cmdText, cmdType, cmdParms);
                obj = cmd.ExecuteScalar();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return obj;
        }
        #endregion


        #region SQLHelper
        public static string GetSqlInsert<T>(T t)
        {
            Type type = t.GetType();
            var tableName = GetTableName(type);
            PropertyInfo[] properties = type.GetProperties();
            string str = "Insert into " + tableName + " ( ";
            foreach (var proper in properties)
            {
                if (IsKey(proper))
                {
                    continue;
                }
                else
                {
                    //str += proper.Name + ",";
                    str += "t$" + proper.Name + ",";
                }
            }
            str = str.Substring(0, str.LastIndexOf(","));
            str += " ) values ( ";
            foreach (var proper in properties)
            {
                if (IsKey(proper))
                {
                    continue;
                }
                else
                {
                    object val = proper.GetValue(t, null);
                    if (val is int || val is float || val is decimal || val is double)
                    {
                        str += proper.GetValue(t, null) + ",";
                    }
                    else if (val is DateTime)
                    {
                        str += "TO_DATE('" + proper.GetValue(t, null) + "','yyyy-mm-dd hh24:mi:ss')" + ",";
                    }
                    else
                    {
                        if (val == null)
                        {
                            str += "null,";
                        }
                        else
                        {
                            str += "'" + proper.GetValue(t, null) + "'" + ",";
                        }

                    }
                }
            }
            str = str.Substring(0, str.LastIndexOf(","));
            str += " )";
            return str;
        }

        public static string GetSqlInsertBatch<T>(List<T> list)
        {
            Type type = list.FirstOrDefault().GetType();
            var tableName = GetTableName(type);
            PropertyInfo[] properties = type.GetProperties();
            StringBuilder sb = new StringBuilder();

            string strAll = "Insert all ";
            foreach (var t in list)
            {
                string str = "into " + tableName + " ( ";
                foreach (var proper in properties)
                {
                    if (IsKey(proper))
                    {
                        continue;
                    }
                    else
                    {
                        str += "t$" + proper.Name + ",";
                    }
                }
                str = str.Substring(0, str.LastIndexOf(","));
                str += " ) values ( ";
                foreach (var proper in properties)
                {
                    if (IsKey(proper))
                    {
                        continue;
                    }
                    else
                    {
                        object val = proper.GetValue(t, null);
                        if (val is int || val is float || val is decimal || val is double)
                        {
                            str += proper.GetValue(t, null) + ",";
                        }
                        else if (val is DateTime)
                        {
                            //str += "TO_DATE('" + proper.GetValue(t, null) + "','MM/DD/YYYY HH:MI:SS AM')" + ",";
                            str += "TO_DATE('" + proper.GetValue(t, null) + "','yyyy-mm-dd hh24:mi:ss')" + ",";
                        }
                        else
                        {
                            if (val == null)
                            {
                                str += "'',";
                            }
                            else
                            {
                                str += "'" + proper.GetValue(t, null) + "'" + ",";
                            }

                        }
                    }
                }
                str = str.Substring(0, str.LastIndexOf(","));
                str += " )";
                sb.AppendLine(str);
            }
            var strEnd = "select 1 from dual";
            var result = strAll + sb.ToString() + strEnd;
            return result;
        }


        public static string GetSqlInsertSPBatch<T>(List<T> list)
        {
            Type type = list.FirstOrDefault().GetType();
            var tableName = GetTableName(type);
            PropertyInfo[] properties = type.GetProperties();
            StringBuilder sb = new StringBuilder();

            string strAll = "Insert all ";
            foreach (var t in list)
            {
                string str = "into " + tableName + " ( ";
                foreach (var proper in properties)
                {
                    if (IsKey(proper))
                    {
                        continue;
                    }
                    else
                    {
                        str +=  proper.Name + ",";
                    }
                }
                str = str.Substring(0, str.LastIndexOf(","));
                str += " ) values ( ";
                foreach (var proper in properties)
                {
                    if (IsKey(proper))
                    {
                        continue;
                    }
                    else
                    {
                        object val = proper.GetValue(t, null);
                        if (val is int || val is float || val is decimal || val is double)
                        {
                            str += proper.GetValue(t, null) + ",";
                        }
                        else if (val is DateTime)
                        {
                            //str += "TO_DATE('" + proper.GetValue(t, null) + "','MM/DD/YYYY HH:MI:SS AM')" + ",";
                            str += "TO_DATE('" + proper.GetValue(t, null) + "','yyyy-mm-dd hh24:mi:ss')" + ",";
                        }
                        else
                        {
                            if (val == null)
                            {
                                str += "'',";
                            }
                            else
                            {
                                str += "'" + proper.GetValue(t, null) + "'" + ",";
                            }

                        }
                    }
                }
                str = str.Substring(0, str.LastIndexOf(","));
                str += " )";
                sb.AppendLine(str);
            }
            var strEnd = "select 1 from dual";
            var result = strAll + sb.ToString() + strEnd;
            return result;
        }

        public static string GetSqlUpdate<T>(T t)
        {
            Type type = t.GetType();
            var tableName = GetTableName(type);
            PropertyInfo[] properties = type.GetProperties();
            string str = "Update " + tableName + " set ";
            var whereStr = string.Empty;
            foreach (var proper in properties)
            {
                if (IsKey(proper))
                {
                    whereStr = " where " + proper.Name + " = " + proper.GetValue(t, null);
                }
                else
                {
                    object val = proper.GetValue(t, null);
                    if (val is int || val is float || val is decimal || val is double)
                    {
                        str += proper.Name + "=" + val + ",";
                    }
                    else if (val is DateTime)
                    {
                        str += proper.Name + "=" + "TO_DATE('" + proper.GetValue(t, null) + "','yyyy-mm-dd hh24:mi:ss')" + ",";
                    }
                    else
                    {
                        str += proper.Name + "='" + val + "',";
                    }
                }
            }
            str = str.TrimEnd(',');
            str = str + whereStr;
            return str;
        }

        public static string GetSqlDelete(string tableName, int id)
        {
            string str = "Delete from " + tableName + " where Id=" + id;
            return str;
        }

        public static string GetSqlDeleteAll(string tableName)
        {
            string str = "Delete from " + tableName;
            return str;
        }

        public static string GetSqlGet(string tableName, int id)
        {
            string str = "select * from " + tableName + " where Id=" + id;
            return str;
        }
        /// <summary>
        ///判断是否是主键
        /// </summary>
        /// <param name="proper"></param>
        /// <returns></returns>
        public static bool IsKey(PropertyInfo proper)
        {
            object[] objs = proper.GetCustomAttributes(typeof(TableAttribute), true);
            bool f = false;
            foreach (var obj in objs)
            {
                if (obj is TableAttribute)
                {
                    TableAttribute alltarget = (TableAttribute)obj;
                    if (alltarget.IsKey)
                    {
                        f = true;
                        break;
                    }
                }
            }
            return f;
        }
        public static string GetTableName(Type type)
        {
            object[] objs = type.GetCustomAttributes(typeof(TableAttribute), true);
            var tableName = string.Empty;
            foreach (var obj in objs)
            {
                if (obj is TableAttribute)
                {
                    TableAttribute alltarget = (TableAttribute)obj;
                    if (alltarget != null)
                    {
                        tableName = alltarget.TableName;
                        break;
                    }
                }
            }
            return tableName;
        }
        #endregion

    }
}
